

Create PROCEDURE [dbo].[Sp_Auto_OURow_Factor]
    @KindFlag SMALLINT,
    @FactorId BIGINT,
    @PersonPercent REAL
AS
DECLARE @GoodsId BIGINT;
DECLARE @MaxPercent REAL;
DECLARE @MaxPrice MONEY;
DECLARE @TotPrice MONEY;
DECLARE @TmpPer REAL;
DECLARE @Price MONEY;
DECLARE @Type SMALLINT;
DECLARE @Id UNIQUEIDENTIFIER;
DECLARE @DeviceShow TINYINT;
DECLARE @IsCalculateTax BIT;
DECLARE @IsDepencencies BIT;
DECLARE @ExchangeTax TINYINT = ISNULL(
                               (
                                   SELECT Value FROM dbo.GeneralSetting WHERE Parameter = 'Exchange_Tax'
                               ),
                               0
                                     );

DECLARE @CalcIncrementalDeductionRow AS TABLE
(
    Id UNIQUEIDENTIFIER
        DEFAULT (NEWID()) PRIMARY KEY,
    GoodsId BIGINT,
    IncrementalDeductionCode INT,
    Name NVARCHAR(30),
    Type CHAR(1),
    MaxPercent dec(18,6),
    Maxprice MONEY,
    IsDepencencies BIT,
    DeviceShow TINYINT,
    IsCalculateTax BIT,
    [Percent] REAL
        DEFAULT (0),
    Qty MONEY,
    Price MONEY
        DEFAULT (0),
    GoodsPrice MONEY,
    TaxFlag CHAR(1)
);

INSERT INTO @CalcIncrementalDeductionRow
(
    GoodsId,
    IncrementalDeductionCode,
    Name,
    Type,
    MaxPercent,
    Maxprice,
    IsDepencencies,
    DeviceShow,
    IsCalculateTax,
    Qty,
    Price,
    TaxFlag
)
SELECT dbo.TmpHavaleh.AutoId GoodsId,
       Over_Under.AutoId,
       Name,
       Type,
      CASE
           WHEN ISNULL(Formula, '0') = 0 THEN
               Max_Percent
           ELSE
       (CASE
            WHEN KalaId.Duty_Flag = 1 THEN
       (CASE
            WHEN TaxPercent <> 0
                 AND Type = 1 THEN
                TaxPercent
            WHEN DiscountPercent <> 0
                 AND Type = 0 THEN
                DiscountPercent
            ELSE
                Max_Percent
        END
       )
            ELSE
                0
        END
       ) 
       END AS Max_Percent,
       Max_Price,
       ISNULL(dbo.Over_Under.Duty_Flag, 0),
       ShowPrice,
       ISNULL(Formula, '0'),
       K_Qty1,
       CASE
           WHEN Kind_Flag IN ( 5, 7, 9, 6, 0 ) THEN
       (CASE
            WHEN @ExchangeTax = 0 THEN
                Price_Forosh
            ELSE
       ((CASE
             WHEN (Price_Forosh < Price_KHarid) THEN
                 0
             ELSE
                 Price_Forosh - Price_KHarid
         END
        )
       )
        END
       )
           WHEN Kind_Flag IN ( 2, 3, 4, 8 ) THEN
               Price_KHarid
       END AS Price,
       Takhfif_Flag
FROM TmpHavaleh WITH (NOLOCK)
    INNER JOIN TmpHeadH WITH (NOLOCK)
        ON TmpHeadH.Id_Havaleh = dbo.TmpHavaleh.Id_Havaleh
    INNER JOIN dbo.KalaId WITH (NOLOCK)
        ON KalaId.K_Code = TmpHavaleh.K_Code
    CROSS JOIN dbo.Over_Under WITH (NOLOCK)
WHERE TmpHeadH.Id_Havaleh = @FactorId
      AND Active = 1
      AND ShowPrice <> (CASE
                            WHEN @KindFlag = 8 THEN
                                -1
                            ELSE
                                0
                        END
                       )
      AND Action_Code = @KindFlag
      AND CONVERT(VARCHAR(10), GETDATE(), 111)
      BETWEEN AzTarikh AND TaTarikh
ORDER BY SortDisplay;

UPDATE @CalcIncrementalDeductionRow
SET Price = 0
WHERE MaxPercent = 0
      AND Maxprice = 0;
DECLARE CursorIncrementalDeductionRow CURSOR FOR
SELECT Id,
       GoodsId,
       Type,
       Qty * Price,
       MaxPercent,
       Maxprice,
       IsCalculateTax,
       IsDepencencies
FROM @CalcIncrementalDeductionRow;

OPEN CursorIncrementalDeductionRow;
FETCH NEXT FROM CursorIncrementalDeductionRow
INTO @Id,
     @GoodsId,
     @Type,
     @Price,
     @MaxPercent,
     @MaxPrice,
     @IsCalculateTax,
     @IsDepencencies;
WHILE @@FETCH_STATUS = 0
BEGIN

    IF @MaxPrice > 0
    BEGIN
        IF (ISNULL(@Price, 0) > @MaxPrice)
        BEGIN

            SET @TmpPer = @MaxPrice * 100 / @Price;



            IF @DeviceShow = 2
                UPDATE @CalcIncrementalDeductionRow
                SET [Percent] = @TmpPer,
                    Price = @MaxPrice
                WHERE Id = @Id;

            ELSE
                UPDATE @CalcIncrementalDeductionRow
                SET [Percent] = @TmpPer,
                    Price = @Price
                WHERE Id = @Id;

        END;
        ELSE
            UPDATE @CalcIncrementalDeductionRow
            SET [Percent] = @MaxPrice * 100 / @Price ,
                Price = @MaxPrice
            WHERE Id = @Id;
    END;
    ELSE IF (@MaxPercent > 0)
    BEGIN



        SET @DeviceShow =
        (
            SELECT DeviceShow FROM @CalcIncrementalDeductionRow WHERE Id = @Id
        );
        --if (@PersonPercent>0)  
        IF (@DeviceShow = 2)
            UPDATE @CalcIncrementalDeductionRow
            SET [Percent] = @PersonPercent,
                Price = @Price * CAST(@PersonPercent AS DEC(16,6)) / 100 
            WHERE Id = @Id;
        ELSE
            UPDATE @CalcIncrementalDeductionRow
            SET [Percent] = @MaxPercent,
                Price =@Price * CAST(@MaxPercent AS dec(16,6)) / 100 
            WHERE Id = @Id;
    END;


    FETCH NEXT FROM CursorIncrementalDeductionRow
    INTO @Id,
         @GoodsId,
         @Type,
         @Price,
         @MaxPercent,
         @MaxPrice,
         @IsCalculateTax,
         @IsDepencencies;
END;
CLOSE CursorIncrementalDeductionRow;
DEALLOCATE CursorIncrementalDeductionRow;

INSERT INTO dbo.Tmp_Over_Under_Row
(
    AutoId,
    Name,
    Type,
    Code_Over_Under,
    Max_Percent,
    Max_Price,
    ShowPrice,
    Price_Row,
    Price,
    [Percent]
)
SELECT GoodsId,
       Name,
       Type,
       IncrementalDeductionCode,
       MaxPercent,
       Maxprice,
       DeviceShow,
       IsCalculateTax,
       Price,
       [Percent]
FROM @CalcIncrementalDeductionRow
WHERE NOT EXISTS
(
    SELECT *
    FROM dbo.Tmp_Over_Under_Row
    WHERE dbo.Tmp_Over_Under_Row.Code_Over_Under = IncrementalDeductionCode
          AND [@CalcIncrementalDeductionRow].GoodsId = dbo.Tmp_Over_Under_Row.AutoId
);

------------------------------------------(Calc Tax Goods Row)--------------------------------------------------
DECLARE CursorTax CURSOR FOR
SELECT GoodsId
FROM @CalcIncrementalDeductionRow
WHERE Type = 1
      AND IsDepencencies = 0
      AND IsCalculateTax = 1
GROUP BY GoodsId;
OPEN CursorTax;
FETCH NEXT FROM CursorTax
INTO @GoodsId;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.Sp_Duty_Row @FactorId, @GoodsId, @KindFlag;
    FETCH NEXT FROM CursorTax
    INTO @GoodsId;
END;
UPDATE dbo.Tmp_Over_Under_Row
SET Price = ROUND(Price, 0)
WHERE AutoId IN
      (
          SELECT AutoId FROM dbo.TmpHavaleh WHERE Id_Havaleh = @FactorId
      )
      AND Price <> ROUND(Price, 0);
CLOSE CursorTax;
DEALLOCATE CursorTax;



